﻿USE FITHOU_EDU
GO
SET dateformat dmy
go

--/*sp Lay toan bo lop hoc
if(OBJECT_ID('sp_tblLophoc_GetAll','p') is not null)
begin
	drop proc sp_tblLophoc_GetAll
end
GO
CREATE proc sp_tblLophoc_GetAll
as
begin
	select * FROM tbl_Lophoc
end
GO
----*/


--/*sp Lay lich hoc theo lop
if(OBJECT_ID('sp_tblLichhoc_GetAllByClassID','p') is not null)
begin
	drop proc sp_tblLichhoc_GetAllByClassID
end
GO
SET dateformat dmy
go
CREATE proc sp_tblLichhoc_GetAllByClassID
	@FK_iLophocID int
as
begin
	SELECT     tbl_Lichhoc.dNgayhoc, tbl_Lichhoc.sGiobatdau, tbl_Lichhoc.sGioketthuc, tbl_Lichhoc.iTrangthai, tbl_Lophoc.sTenlop, tbl_Nguoidung.sHoten, tbl_Phonghoc.sTenphong, 
                      tbl_Monhoc.sTenmonhoc
	FROM         tbl_Lichhoc INNER JOIN
						  tbl_Lophoc ON tbl_Lichhoc.FK_iLophocID = tbl_Lophoc.PK_iLophocID INNER JOIN
						  tbl_Nguoidung ON tbl_Lichhoc.FK_sGiangvienID = tbl_Nguoidung.PK_sNguoidungID INNER JOIN
						  tbl_Phonghoc ON tbl_Lichhoc.FK_iPhonghocID = tbl_Phonghoc.PK_iPhonghocID INNER JOIN
						  tbl_Monhoc ON tbl_Lichhoc.FK_iMonhocID = tbl_Monhoc.PK_iMonhocID
	WHERE FK_iLophocID = @FK_iLophocID
end
GO
--EXEC sp_tblLichhoc_GetAllByClassID 1
--*/

--/*sp Kiểm tra đăng nhập
if(OBJECT_ID('sp_ValidLogin','p') is not null)
begin
	drop proc sp_ValidLogin
end
GO
CREATE proc sp_ValidLogin
	@PK_sNguoidungID varchar(20), @sMatkhau varchar(50)
as
begin
	select * FROM tbl_Nguoidung WHERE PK_sNguoidungID = @PK_sNguoidungID AND sMatkhau = @sMatkhau
end
GO
--SELECT * FROM tbl_Nguoidung
--EXEC sp_ValidLogin 'SV08B601', '0'
----*/


--/*sp Lấy lịch giảng theo giảng viên
if(OBJECT_ID('sp_tblLichhocGetByGiangvienID','p') is not null)
begin
	drop proc sp_tblLichhocGetByGiangvienID
end
GO
CREATE proc sp_tblLichhocGetByGiangvienID
	@PK_sNguoidungID varchar(20)
as
begin
	SELECT     tbl_Lichhoc.PK_iLichhocID, tbl_Nguoidung.sHoten, tbl_Lichhoc.dNgayhoc, tbl_Lichhoc.sGiobatdau, tbl_Lichhoc.sGioketthuc, tbl_Lichhoc.iTrangthai, tbl_Monhoc.sTenmonhoc, 
                      tbl_Lophoc.sTenlop, tbl_Phonghoc.sTenphong
	FROM         tbl_Lichhoc INNER JOIN
                      tbl_Lophoc ON tbl_Lichhoc.FK_iLophocID = tbl_Lophoc.PK_iLophocID INNER JOIN
                      tbl_Monhoc ON tbl_Lichhoc.FK_iMonhocID = tbl_Monhoc.PK_iMonhocID INNER JOIN
                      tbl_Phonghoc ON tbl_Lichhoc.FK_iPhonghocID = tbl_Phonghoc.PK_iPhonghocID INNER JOIN
                      tbl_Nguoidung ON tbl_Lichhoc.FK_sGiangvienID = tbl_Nguoidung.PK_sNguoidungID
    WHERE tbl_Lichhoc.FK_sGiangvienID = @PK_sNguoidungID AND CONVERT(CHAR(10), tbl_Lichhoc.dNgayhoc,103) >= CONVERT(CHAR(10),GETDATE(),103)
END
--EXEC sp_tblLichhocGetByGiangvienID 'GV001'
--SELECT * FROM tbl_Lichhoc WHERE FK_sGiangvienID = 'GV001' AND dNgayhoc > getdate()
--SELECT CONVERT(CHAR(10),GETDATE(),103) 
--=======================================
